#### 2020, Read in data ####
ced2020 <- read_xlsx("data/ca-local-election-data/CEDA2020 Data.xlsx") %>% 
  select(First,
         LAST,
         PERCENT,
         ELECTED,
         `CO#`,
         `VOTE#`,
         CNTYNAME,
         NUM_INC,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RACEID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2020) %>% 
  rename( 
    fname = First, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO#`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = NUM_INC,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RACEID,
    jur = JUR,
    place = PLACE
  )


#### 2018, Read in data ####
ced2018 <- read_xlsx("Data/ca-local-election-data/CEDA 2018 Data (1).xlsx",
                     sheet = 1) %>% 
  select(First,
         LAST,
         PERCENT,
         ELECTED,
         `CO#`,
         `VOTE#`,
         CNTYNAME,
         NUM_INC,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RACEID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2018) %>% 
  rename( 
    fname = First, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO#`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = NUM_INC,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RACEID,
    jur = JUR,
    place = PLACE
  )

#### 2016, Read in data ####
ced2016 <- read_xlsx("Data/ca-local-election-data/CEDA2016Data.xlsx",
                     sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         NUM_INC,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RACEID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2016) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = NUM_INC,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RACEID,
    jur = JUR,
    place = PLACE
  )


#### 2014, Read in data ####
ced2014 <- read_xlsx("Data/ca-local-election-data/CEDA2014Data.xlsx",
                     sheet = 2) %>% 
  select(FIRST,
         LAST,
         Percent,
         elected,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         NUM_INC,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RACEID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2014) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = Percent,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = NUM_INC,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RACEID,
    jur = JUR,
    place = PLACE
  )


#### 2012, Read in data ####
ced2012 <- read_xlsx("Data/ca-local-election-data/CEDA2012Data.xlsx",
                     sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         NUM_INC,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2012) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = NUM_INC,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )


#### 2010, Read in data ####
ced2010 <- read_xls("Data/ca-local-election-data/CEDA2010Data.xls",
                     sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2010) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### 2008, Read in data ####
ced2008 <- read_xls("Data/ca-local-election-data/CEDA2008Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2008) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### 2006, Read in data ####
ced2006 <- read_xls("Data/ca-local-election-data/CEDA2006Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2006) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### 2004, Read in data ####
ced2004 <- read_xls("Data/ca-local-election-data/CEDA2004Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2004) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )


#### 2002, Read in data ####
ced2002 <- read_xls("Data/ca-local-election-data/CEDA2002Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2002) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### 2000, Read in data ####
ced2000 <- read_xls("Data/ca-local-election-data/CEDA2000Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 2000) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### 1998, Read in data ####
ced1998 <- read_xls("Data/ca-local-election-data/CEDA1998Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 1998) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### 1996, Read in data ####
ced1996 <- read_xls("Data/ca-local-election-data/CEDA1996Data.xls",
                    sheet = 1) %>% 
  select(FIRST,
         LAST,
         PERCENT,
         ELECTED,
         `CO`,
         `VOTE#`,
         CNTYNAME,
         Num_Inc,
         TOTVOTES,
         `CAND#`,
         OFFICE,
         RaceID,
         JUR,
         PLACE,
         Multi_RaceID,
         Multi_CandID,
         Multi_CO) %>% 
  mutate(year = 1996) %>% 
  rename( 
    fname = FIRST, 
    lname = LAST,
    percent = PERCENT,
    elected = ELECTED,
    co_num = `CO`,
    num_seats = `VOTE#`,
    co_name = CNTYNAME,
    inc = Num_Inc,
    totvotes = TOTVOTES,
    num_cand = `CAND#`,
    office = OFFICE,
    raceid = RaceID,
    jur = JUR,
    place = PLACE
  )

#### Merge all years ####
df <- rbind(ced2020,
            ced2018,
            ced2016,
            ced2014,
            ced2012,
            ced2010,
            ced2008,
            ced2006,
            ced2004,
            ced2002,
            ced2000,
            ced1998,
            ced1996)

#### Save out data ####
write_csv(df, "Data/ca-local-election-data/ced-merged.csv")
rm(list = ls())
